﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Globalization;
using System.Xml;

using SpectationClient;
using SpectationClient.DataBaseDescription;
using SpectationClient.GUI;
using SpectationClient.DGVExtensions;
using SpectationClient.SQLCommandBuilder;

using Npgsql;

namespace SpectationClient.Stuff {
    

    public class FormHelper {

        # region ComboBox Initialization
        private static string CNMP_VAL = "VALUEMEMBER";
        private static string CNMP_DISP = "DISPLAYMEMBER";

        public static string VALUEMEMBER { get { return CNMP_VAL; } }
        public static string DISPLAYMEMBER { get { return CNMP_DISP; } }

        /// <summary>
        /// Initializes a CheckedListBox with values from a data table.
        /// </summary>
        /// <param name="clb"></param>
        /// <param name="dt"></param>
        /// <param name="ti"></param>
        /// <param name="displayColums"></param>
        /// <param name="delimiter"></param>
        public static void initCheckedListBox(CheckedListBox clb, DataTable dt, TableInfo ti, String[] displayColums=null, String delimiter=" ") {
            if(displayColums == null) displayColums = (from c in ti.Values select c.Name).ToArray();

            Key pk = ti.PrimaryKey;
            foreach(DataRow r in dt.Rows) {
                Object[] keyValues = (from c in pk.Names
                                      select r[c]).ToArray();

                String displayString = String.Join(delimiter,
                                       (from c in displayColums
                                        where r[c] != null
                                        select r[c].ToString()).ToArray());

                BoxItem item = new BoxItem(displayString, 
                                            pk.Count == 1 ? keyValues[0] : keyValues);
                clb.Items.Add(item, false);
               
            }
           
        
        
        }


      
        public static void initComboBoxItems(ComboBox cb, List<ColumnInfo.ConstrainedValue> constrainedValueList, bool firstEmpty=true) {
            DataTable dt = new DataTable();
            dt.Columns.Add(CNMP_VAL, constrainedValueList[0].value.GetType());
            dt.Columns.Add(CNMP_DISP, typeof(String));
            foreach(ColumnInfo.ConstrainedValue cv in constrainedValueList) {
                dt.Rows.Add(new Object[] { cv.value, cv.tooltip });
            }


            if(firstEmpty) dt.Rows.InsertAt(dt.NewRow(), 0);
            initComboBoxItems(cb, dt, CNMP_VAL, CNMP_DISP);
        }

        
        public static void initComboBoxItems(ComboBox cb, DataTable dt, String valueMember, String displayMember) {
            cb.DisplayMember = displayMember;
            cb.ValueMember = valueMember;
            cb.DataSource = dt;
            cb.SelectedIndex = -1;
        }
       

        /// <summary>
        /// Initializes a ComboBoxColumn with values that constrain a id_column (SQL-Constraints)
        /// </summary>
        /// <param name="cb"></param>
        /// <param name="Schema"></param>
        /// <param name="tableName"></param>
        /// <param name="id_column"></param>
        /// <param name="firstEmpty"></param>
        public static void initComboBoxItems(DataGridViewComboBoxColumn cb, List<ColumnInfo.ConstrainedValue> constrained_values, bool firstEmpty=true) {
            DataTable dt = new DataTable();
            dt.Columns.Add(CNMP_VAL, constrained_values[0].value.GetType());
            dt.Columns.Add(CNMP_DISP, typeof(String));
            if(firstEmpty) dt.Rows.Add(new Object[] { null, null });
            foreach(ColumnInfo.ConstrainedValue cv in constrained_values) {
                dt.Rows.Add(new Object[] { cv.value, cv.tooltip });
            }
            cb.DataSource = dt;
            cb.DisplayMember = CNMP_DISP;
            cb.ValueMember = CNMP_VAL;
        }

        /// <summary>
        /// Initializes a DataGridViewComboBoxColumn with values of a data table.
        /// </summary>
        /// <param name="cb"></param>
        /// <param name="dt"></param>
        /// <param name="ti"></param>
        /// <param name="displayColums"></param>
        /// <param name="delimiter"></param>
        public static void initComboBoxItems(DataGridViewComboBoxColumn cb, DataTable dt, TableInfo ti, bool firstItemEmpty, String[] dispayColums=null, String delimiter=" ") {
            
            if(firstItemEmpty) cb.Items.Add(new BoxItem("",null));

            cb.ValueMember = "Value";
            cb.DisplayMember = "Display";

            if(dispayColums == null) dispayColums = (from c in ti.Values select c.Name).ToArray();
            Key pk = ti.PrimaryKey;
            foreach(DataRow r in dt.Rows) {
                String displayString = String.Join(delimiter,
                                       (from c in dispayColums
                                        where r[c] != null
                                        select r[c].ToString()).ToArray());
                BoxItem bi;
                if(pk.Names.Count > 1) {
                    Object[] keyValues = (from c in pk.Names
                                          select r[c]).ToArray();

                    bi = new BoxItem(displayString, keyValues);
                } else {
                    bi = new BoxItem(displayString, r[pk.Names[0]]);
                }
               
                cb.Items.Add(bi);
               // dtNew.Rows.Add(new Object[] { keyValues, displayString });
            }
           // cb.DataSource = dtNew;
            
        }

        public static void initComboBoxItems(ComboBox cb, DataTable dt, TableInfo ti) {
            FormHelper.initComboBoxItems(cb, dt, ti, true);
        
        }

        /// <summary>
        /// Initializes a Combobox with values of a data table.
        /// </summary>
        /// <param name="cb">The Combobox to fill with values</param>
        /// <param name="dt">The data table to take the values from</param>
        /// <param name="ti">The TableInfo to retrieve the Primare Key Columns from to choose as combobox value members.</param>
        /// <param name="displayColums">Array to specify the colums form ti to choose for displaying.</param>
        /// <param name="delimiter">Delimiter between displayed table iPoint values</param>
        public static void initComboBoxItems(ComboBox cb, DataTable dt, TableInfo ti, bool firstEmpty, String[] displayColums=null, String delimiter=" ") {
            DataTable dtNew = new DataTable();
            dtNew.Columns.Add(CNMP_VAL, typeof(Object[]));
            dtNew.Columns.Add(CNMP_DISP, typeof(String));

            cb.ValueMember = CNMP_VAL;
            cb.DisplayMember = CNMP_DISP;
            if(firstEmpty) dtNew.Rows.Add(new Object[] { null, null});
            if(displayColums == null) displayColums = (from c in ti.Values select c.Name).ToArray();
            
            Key pk = ti.PrimaryKey;
            foreach(DataRow r in dt.Rows) {
                Object[] keyValues = (from c in pk.Names
                                         select r[c]).ToArray();
                
                String displayString = String.Join(delimiter, 
                                       (from c in displayColums
                                        where r[c] != null 
                                        select r[c].ToString()).ToArray());
                
                dtNew.Rows.Add(new Object[]{keyValues, displayString});
            }
            cb.DataSource = dtNew;
            cb.SelectedIndex = -1;
        }

        # endregion ComboBox Initialization

        public static void clearForms(List<Object> FormsToClear) {
            FormHelper.clearForms(FormsToClear, DateTime.Today);
        }
        public static void clearForms(List<Object> FormsToClear, DateTime ignoreTime) { 
            foreach(Object o in FormsToClear){
                TextBox tb = o as TextBox;
                if(tb != null) tb.Clear();

                ComboBox cb = o as ComboBox;
                if(cb != null) cb.SelectedIndex = -1;

                ListBox lb = o as ListBox;
                if(lb != null) lb.SelectedIndices.Clear();

                CheckBox c = o as CheckBox;
                if(c != null) c.Checked = false;
                DateTimePicker dtp = o as DateTimePicker;
                if(dtp != null) dtp.Value = ignoreTime;
            }
        
        }

        public static void removeInfoFromSelectionTable(Object sender, EventArgs e) {
            if(sender is Control) FormHelper.removeInfoFromSelectionTable(sender as Control);
        }
        public static void removeInfoFromSelectionTable(Control control) {
            control.Tag = null;
            control.Text = null;
        }

        public static void setInfoFromSelectionTable(Control control,  DBManager dbm, String schema, String table) {
            FormHelper.setInfoFromSelectionTable(control, dbm, schema, table, null, "Auswahl übernehmen");
        }

        public static void setInfoFromSelectionTable(Control control, DBManager dbm, String schema, String table, String titleText, String okButtonText){
            SubShowSelectionTable SSS = new SubShowSelectionTable(ref dbm, schema, table, false, true, titleText, okButtonText);
            if (SSS.ShowDialog() == DialogResult.OK) {
                List<Object[]> ids = SSS.getSelectedIDs();
                List<Object[]> display = SSS.getSelectedValues();
                if(ids.Count > 0) {
                        control.Tag = ids[0];
                        control.Text = TextHelper.combine<Object>(display[0], " ");
                }
            }
        }

        public static void setInfoFromSelectionTable(Control controlValueText, Control controlIDText, DBManager dbm, String schema, String table) {
            FormHelper.setInfoFromSelectionTable(controlValueText, controlIDText, dbm, schema, table, null, null);
        }

        public static void setInfoFromSelectionTable(Control controlValueText, Control controlIDText, DBManager dbm, String schema, String table, String titleText, String okButtonText) {
            SubShowSelectionTable SSS = new SubShowSelectionTable(ref dbm, schema, table, false, true, titleText, okButtonText);
            if(SSS.ShowDialog() == DialogResult.OK) {
                List<Object[]> ids = SSS.getSelectedIDs();
                List<Object[]> display = SSS.getSelectedValues();
                if(ids.Count > 0) {
                    controlValueText.Text = TextHelper.combine<Object>(display[0], " ");
                    controlIDText.Text = TextHelper.combine<Object>(ids[0], " ");
                    
                }
            }
        }
      

        /// <summary>
        /// Adds infos from selection tableName to a DGV Cell
        /// </summary>
        /// <param name="DGV">The DGV</param>
        /// <param name="iPoint">iPoint to add the info</param>
        /// <param name="column">column to add the info</param>
        /// <param name="dbm">DatabaseManager</param>
        /// <param name="Schema">Speclib Schema</param>
        /// <param name="tableName">Speclib Table</param>
        /// <param name="titleText">Title of Selection Form</param>
        /// <param name="okButtonText">Text of OK button</param>
        public static void setInfoFromSelectionTable(DataGridViewCell sender, DataGridViewColumn targetColumn, List<String> displayedColumns,
                    DBManager dbm, String schema, String table, bool multipeSelection, String titleText, String okButtonText) {
            DataGridView dgv = sender.DataGridView;


            SubShowSelectionTable SSS = new SubShowSelectionTable(ref dbm, schema, table, multipeSelection, false, titleText, okButtonText);
            
            if(SSS.ShowDialog() == DialogResult.OK) {
                List<Object[]> ids = SSS.getSelectedIDs();
                List<Object[]> vals = (displayedColumns == null)? SSS.getSelectedValues() : SSS.getSelectedValues(displayedColumns.ToArray());
                //int iRow = sender.RowIndex;
                //DGV.Rows.Add(3);
                int iRow0 = sender.RowIndex;
                for(int i = 0; i < ids.Count; i++){
                    int iRow = iRow0 + i;
                    if(dgv.RowCount <= iRow + 1) dgv.Rows.Add();
                    DataGridViewCell cell = dgv[targetColumn.Name, iRow];
                    cell.Tag = ids[i][0];
                    cell.Value = TextHelper.combine(vals[i], " ");
                    //String stop = "";
   
                }
            }
        }


        /// <summary>
        /// This method opens an Selection Form and adds all selected rows to the appendix DataTable of an DGV.
        /// </summary>
        /// <param name="DGV">DGV</param>
        /// <param name="addToExistingRows">if true, existing rows will not be deleted in the DGV</param>
        /// <param name="dbm">Database Manager</param>
        /// <param name="Schema">SPECLIB Schema</param>
        /// <param name="tableName">SPECLIB Table</param>
        /// <param name="titleText">Title SelectionForm</param>
        /// <param name="okButtonText">Button Text Seleciton Form</param>
        public static void setInfoFromSelectionTable(
            DataGridView dgv, bool addToExistingRows, DBManager dbm, String schema, String table, 
            bool allowMultipleSelection, String titleText, String okButtonText) {
            SubShowSelectionTable SSS = new SubShowSelectionTable(ref dbm, schema, table, allowMultipleSelection, false, titleText, okButtonText);
            TableInfo ti = dbm.getTableInfo(schema, table);
            if(SSS.ShowDialog() == DialogResult.OK) {
                DataTable dt = SSS.getSelectedRows();
                
                dgv.SuspendLayout();
                if(dgv.DataSource == null) {
                    dgv.DataSource = dt;
                } else {
                    DataTable dt_dgv = (DataTable)dgv.DataSource;

                    dt_dgv.BeginLoadData();
                    if(!addToExistingRows && dt.Rows.Count > 0) {
                        dt_dgv.Rows.Clear();
                    } else {
                        //check whether ids are already present
                        List<DataRow> redundant = DataHelper.EqualRows(dt_dgv, dt);
                        foreach(DataRow row in redundant) dt.Rows.Remove(row);
                    }
                    foreach(DataRow dr in dt.Rows) {
                        dt_dgv.Rows.Add(dr.ItemArray);
                    }
                    dt_dgv.EndLoadData();
                }
                
                dgv.ResumeLayout();
                dgv.Refresh();
            }
        }

        public static void setFlexibleHeight(DataGridView dgv, int minHeight, int maxHeight){
            int fixed_width  = dgv.Size.Width;
            dgv.AutoSize = true;
            dgv.MinimumSize = new System.Drawing.Size(fixed_width, minHeight);
            dgv.MaximumSize = new System.Drawing.Size(fixed_width, maxHeight);
            if(dgv.Rows.Count <=1) dgv.Size = new System.Drawing.Size(fixed_width, minHeight);
        }
        
        public static void setFlexibleWidth(DataGridView dgv, int minWidth, int maxWidth) {
            int fixed_height = dgv.Size.Height;
            dgv.AutoSize = true;
            dgv.MinimumSize = new System.Drawing.Size(minWidth, fixed_height);
            dgv.MaximumSize = new System.Drawing.Size(maxWidth, fixed_height);
            
        }

        public static DateTime getMostExactTime(DateTimePicker YearMonDay) {
            return FormHelper.getMostExactTime(YearMonDay, null, DateTimeKind.Unspecified);
        }
        public static DateTime getMostExactTime(DateTimePicker YearMonDay, DateTimePicker HrsMinSeconds) {
            return FormHelper.getMostExactTime(YearMonDay, HrsMinSeconds, DateTimeKind.Unspecified);
        }
        public static DateTime getMostExactTime(DateTimePicker YearMonDay, DateTimePicker HrsMinSeconds, DateTimeKind dtk) {
            DateTime dnull = new DateTime(1, 1, 1, 0, 0, 0, 0, dtk);
            DateTime d1,d2;
            if(YearMonDay != null && YearMonDay.Checked){
                d1 = YearMonDay.Value;
            } else{
                d1 = dnull;
            }
            if(HrsMinSeconds != null && HrsMinSeconds.Checked) {
                d2 = HrsMinSeconds.Value;
            } else {
                d2 = dnull;
            }
            return new DateTime(d1.Year, d1.Month, d1.Day, d2.Hour, d2.Minute, d2.Second, d2.Millisecond, dtk);
            ;
        }

      

        /// <summary>
        /// Connects a CheckBox with a Control (e.g. a GroupBox). When the CheckBox is checked, the Control will 
        /// be visible and vice versa
        /// </summary>
        /// <param name="sender">The Checkbox whose cheched key is to control the visibility of the Control</param>
        /// <param name="target">The Target Control</param>
        public static void setLinkAction_SwitchVisualization(CheckBox sender, Control target){
            FormHelper.setLinkAction(sender, target, linkAction.boolSwitchVisualization);
        }
        public static void setLinkAction_SwitchVisualization(RadioButton sender, Control target) {
            FormHelper.setLinkAction(sender, target, linkAction.boolSwitchVisualization);
        }
        public static void setLinkAction_RemoveSelectedRows(Button sender, DataGridView dgv) {
            FormHelper.setLinkAction(sender, dgv, linkAction.removeSelectedRows);
        }
        public static void setLinkAction_SwitchEnabledIfFilled(DataGridView sender, Control target) {
            FormHelper.setLinkAction(sender, target, linkAction.switchEnabledIsFilled);
        }

        private static void setLinkAction(Object sender, object target, linkAction actionOnTarget){
            Dictionary<linkAction,List<object>> actionDic = getLinkActionDictionary(sender, true);
            if(!actionDic.ContainsKey(actionOnTarget)) actionDic.Add(actionOnTarget, new List<object>());
            actionDic[actionOnTarget].Add(target);
            
            //init sender specific event handlers
            if(sender is CheckBox) {
                CheckBox cb = (CheckBox)sender;
                cb.CheckedChanged += new EventHandler(linkActions_doAction);
                linkActions_doAction(sender, new EventArgs());
            } else if(sender is RadioButton) {
                RadioButton rb = (RadioButton)sender;
                rb.CheckedChanged += new EventHandler(linkActions_doAction);
                linkActions_doAction(sender, new EventArgs());
            } else if(sender is DataGridViewCell) {
                DataGridViewCheckBoxCell cell = (DataGridViewCheckBoxCell)sender;
                //String stop = "";
                linkActions_doAction(sender, new EventArgs());
            } else if(sender is Button) {
                Button b = (Button)sender;
                b.Click += new EventHandler(linkActions_doAction);
            /*} else if(sender is DataTable) {
                DataTable dt = (DataTable)sender;
                if(actionOnTarget == linkAction.switchEnabledIsFilled) {
                    dt.RowDeleted +=new DataRowChangeEventHandler(linkActions_doAction);
                    dt.TableNewRow +=new DataTableNewRowEventHandler(linkActions_doAction);
                } else {
                    throw new Exception("Automatic event-handler is not implemented");
                }*/
            } else if(sender is DataGridView) {
                DataGridView dgv = (DataGridView)sender;
                if(actionOnTarget == linkAction.switchEnabledIsFilled) {
                    dgv.RowsAdded += new DataGridViewRowsAddedEventHandler(linkActions_doAction);
                    dgv.RowsRemoved +=new DataGridViewRowsRemovedEventHandler(linkActions_doAction);
                } else {
                    throw new Exception("Automatic event-handler is not implemented");
                }
            } else {
                throw new Exception("Automatic event-handler is not implemented");
            }  
        }

        
        
        private static Dictionary<linkAction,List<object>> getLinkActionDictionary(object sender, bool initialise){
            Object tag_value = null;
            if(sender is Control) {
                tag_value = ((Control)sender).Tag;
            } else if(sender is DataGridViewCell) {
                tag_value = ((DataGridViewCell)sender).Tag;
            } else {
                throw new Exception("Not implemented!");
            }
            
            if(tag_value == null) {
                if(initialise) {
                    tag_value = new Dictionary<linkAction, List<object>>();
                } else {
                    throw new Exception("Programming Error: actionDictionary expected but not found!");
                }
            } else if(tag_value != null && !(tag_value is Dictionary<linkAction, List<object>>)){
                throw new Exception("Programming Error: Tag value is not an ActionDictionary!");
            }
            Dictionary<linkAction, List<object>> actionDic = (Dictionary<linkAction, List<object>>) tag_value;
            if(sender is Control) {
                ((Control)sender).Tag = actionDic;
            } else if(sender is DataGridViewCell) {
                ((DataGridViewCell)sender).Tag = actionDic;
            }
            return actionDic;
        }

        public enum linkAction{
            /// <summary>
            /// Hides visible form-object and vice versa.
            /// </summary>
            boolSwitchVisualization,
            /// <summary>
            /// Removes selected values from DGV and whatever will come...
            /// </summary>
            removeSelectedRows,
            /// <summary>
            /// Enables/Disables 
            /// </summary>
            boolSwitchEnabled,
            switchEnabledIsFilled
        }
    
        private static void linkActions_doAction(object sender, EventArgs e) {
            Dictionary<linkAction,List<object>> actionDic = getLinkActionDictionary(sender, false);
            foreach(linkAction l in actionDic.Keys){
                switch(l) { 
                    case linkAction.boolSwitchEnabled:
                        linkActions_doAction_boolSwitchEnabled(sender, actionDic[l]);           
                        break;
                    case linkAction.boolSwitchVisualization:
                        linkActions_doAction_boolSwitchVisualization(sender, actionDic[l]);
                        break;
                    case linkAction.removeSelectedRows:
                        linkActions_doAction_removeSelectedRows(actionDic[l]);
                        break;
                    case linkAction.switchEnabledIsFilled:
                        linkActions_doAction_switchIsFilled(sender, actionDic[l]);
                        break;
                    default: throw new Exception("Programming error");
                }
            }
        }

        private static void linkActions_doAction_switchIsFilled(object sender, List<object> form_objects) {
            bool enabled;
            if(sender is DataTable){
                enabled = ((DataTable)sender).Rows.Count > 0;
            } else if(sender is DataGridView) {
                enabled = ((DataGridView)sender).RowCount > 0;
            } else {
                throw new Exception("Not implemented!");            
            }
            
            foreach(object o in form_objects) {
                if(o is Control) {
                    ((Control)o).Enabled = enabled;
                } else {
                    throw new Exception("Not implemented!");
                }
            }
        }

        private static void linkActions_doAction_boolSwitchEnabled(Object c, List<object> form_objects) {
            bool enabled = false;
            if(c is CheckBox){ enabled = ((CheckBox) c).Checked;} else
            if(c is RadioButton){enabled = ((RadioButton)c).Checked;} else{
                throw new Exception("Not implemented!");
            }

            foreach(object o in form_objects) {
                if(o is Control) {
                    ((Control)o).Enabled = enabled;
                } else {
                    throw new Exception("Not implemented!");
                }
            }
        }
        private static void linkActions_doAction_boolSwitchVisualization(Object c, List<object> form_objects) {
            bool visible = false;
            if(c is CheckBox) { visible = ((CheckBox)c).Checked; } else
            if(c is RadioButton) { visible = ((RadioButton)c).Checked; } else {
                    throw new Exception("Not implemented!");
            }

            foreach(object o in form_objects) {
                if(o is Control) {
                    ((Control)o).Visible = visible;
                } else {
                    throw new Exception("Not implemented!");
                }
            }
        }

        private static void linkActions_doAction_removeSelectedRows(List<object> form_objects) {
            foreach(object o in form_objects) {
                if(o is DataGridView) {
                    DataGridView dgv = (DataGridView) o;
                    dgv.SuspendLayout();
                    if(dgv.DataSource is DataTable) {
                        DataTable dt = (DataTable)dgv.DataSource;
                        DataGridViewSelectedRowCollection rc = dgv.SelectedRows;
                        foreach(DataGridViewRow dgv_row in rc) {
                            dgv.Rows.Remove(dgv_row);
                        }
                    } else {
                        DataGridViewSelectedRowCollection rc = dgv.SelectedRows;
                        foreach(DataGridViewRow dgv_row in rc) {
                            dgv.Rows.Remove(dgv_row);
                        }
                    }
                    dgv.ResumeLayout();
                } else {
                    throw new Exception("Not implemented!");
                }
            }
        }


        public static SQLCommandBuilder.Condition getCondition(DataGridView dgv, String dgvColumn, String sqlColumn, Condition.Op op) {
            Condition condition = new Condition(sqlColumn, op);
            return FormHelper.getCondition(dgv, dgvColumn, condition);
        }


        public static SQLCommandBuilder.Condition getCondition(DataGridView dgv, String dgvColumn, Condition condition) { 
            for(int i=0; i < dgv.RowCount; i++){
                condition.Add(dgv[dgvColumn,i].Value);
            }
            return condition;
        }

        /// <summary>
        /// Initializes a DataGridView by the values of an DataTable.
        /// If set, the TableInfo information is considered to map the DataTables CLI data types to other values, 
        /// e.g. byte[] to Images/Files/Spectra
        /// </summary>
        /// <param name="DGV"></param>
        /// <param name="dt"></param>
        /// <param name="ti"></param>
        /// <param name="dv"></param>
        public static void initDGVColumns(DataGridView DGV, DataTable dt, TableInfo ti=null, DataView dv=null, NpgsqlConnection connection = null) {
            
            DGV.SuspendLayout();
            DGV.DataSource = null;
            DGV.Columns.Clear();
            DGV.AutoGenerateColumns = false;

            List<DataGridViewColumn> newDGVColumns = new List<DataGridViewColumn>();
            //CMS_DGV.changeTableInfo(RecentTableInfo);
            //CMS_DGV.ID_COLUMNS = RecentTableInfo.PK;

            if(ti == null) ti = new TableInfo("empty");
            try {
                foreach(DataColumn dataColumn in dt.Columns) {
                    DataGridViewColumn dgvColumn;

                    if(!ti.ContainsKey(dataColumn.ColumnName)) {
                        if(dataColumn.DataType != typeof(Byte[])) {
                            dgvColumn = new DataGridViewTextBoxColumn();
                        } else {
                            dgvColumn = new DataGridViewByteArrayColumn();
                            ((DataGridViewByteArrayColumn)dgvColumn).setToolStripLabels(true, DGV.AllowUserToDeleteRows);
                        }

                        dgvColumn.SortMode = DataGridViewColumnSortMode.Automatic;
                        dgvColumn.DataPropertyName = dataColumn.ColumnName;
                        dgvColumn.Name = dataColumn.ColumnName;

                        newDGVColumns.Add(dgvColumn);
                        DGV.Columns.Add(dgvColumn);
                    } else {


                        ColumnInfo columnInfo = ti[dataColumn.ColumnName];
                        if(columnInfo is ColumnInfoGeometry) {
                            ColumnInfoGeometry cig = columnInfo as ColumnInfoGeometry;
                            dgvColumn = new DGVExtensions.DataGridViewGeometryColumn(cig.SRID);


                        } else if(columnInfo is ColumnInfoImageFile) {
                            DataGridViewPhotoColumn PhotoColumn = new DataGridViewPhotoColumn(columnInfo as ColumnInfoImageFile, connection);
                            dgvColumn = PhotoColumn;




                        } else if(columnInfo is ColumnInfoSpectralFile) {
                            dgvColumn = new DataGridViewSpectrumColumn((ColumnInfoSpectralFile)columnInfo);
                            ((DataGridViewSpectrumColumn)dgvColumn).setToolStripLabels(true, true, false);
                        } else if(columnInfo is ColumnInfoFile) {
                            dgvColumn = new DataGridViewByteArrayColumn((ColumnInfoFile)columnInfo);
                            ((DataGridViewByteArrayColumn)dgvColumn).setToolStripLabels(true, DGV.AllowUserToDeleteRows);

                        } else {

                            if(dataColumn.DataType != typeof(Byte[])) {
                                dgvColumn = new DataGridViewTextBoxColumn();
                            } else {
                                dgvColumn = new DataGridViewByteArrayColumn();
                                ((DataGridViewByteArrayColumn)dgvColumn).setToolStripLabels(true, DGV.AllowUserToDeleteRows);
                            }

                            dgvColumn.ReadOnly = columnInfo.IsAutogenerated;
                            
                        }

              
                        dgvColumn.SortMode = DataGridViewColumnSortMode.Automatic;
                        dgvColumn.DataPropertyName = dataColumn.ColumnName;
                        dgvColumn.Name = columnInfo.Name;

                        newDGVColumns.Add(dgvColumn);
                        DGV.Columns.Add(dgvColumn);
                    }
                }
            } catch(Exception e) { 
                MessageBox.Show(TextHelper.Exception2String(e), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            } finally {

                //DGV.Columns.AddRange(newDGVColumns.ToArray());
                if(dv != null) {
                    dv.Table = dt;
                    DGV.DataSource = dv;
                } else {
                    DGV.DataSource = dt;
                }
                
                DGV.ResumeLayout();
            }
        }

        public static bool containsControl(Control containerControl, Object sender) {
            if(sender is Control) return FormHelper.containsControl(containerControl, (Control)sender);
            return false;
        }

        public static bool containsControl(Control containerControl, Control targetControl) {
            if(containerControl == targetControl) return true;
            if(containerControl.Controls.Contains(targetControl)) return true;
            bool result = false;
            foreach(Control c in containerControl.Controls) {
                if(c is GroupBox) result = result || FormHelper.containsControl((GroupBox)c, targetControl);
               if(c is Panel) result = result  || FormHelper.containsControl((Panel) c, targetControl);
            }
            return result;
        }


        public static void removeEmptyRows(DataGridView dataGridView) {
            List<DataGridViewRow> rowsToRemove = new List<DataGridViewRow>();
            foreach(DataGridViewRow row in dataGridView.Rows) {
                bool remove = true;
                foreach(DataGridViewCell cell in row.Cells) {
                    remove &= cell.Value == null || cell.Value == DBNull.Value;
                }
                if(remove) rowsToRemove.Add(row);
            }

            foreach(DataGridViewRow row in rowsToRemove) {
                dataGridView.Rows.Remove(row);
            }
        }

        public static void removeSelectedRows(object sender, EventArgs e) {
            FormHelper.removeSelectedRows((DataGridView)sender);
        }

        public static void removeSelectedRows(DataGridView dataGridView) {
            var selectedRows = (from DataGridViewRow row in dataGridView.Rows
                               where row.Selected
                               select row).ToList();

            foreach(DataGridViewRow row in selectedRows) {
                dataGridView.Rows.Remove(row);
            }
        }

        public static void removeSelectedCells(DataGridView dataGridView) {
            foreach(DataGridViewCell cell in dataGridView.SelectedCells) {
                cell.Value = null;
            }
            FormHelper.removeSelectedRows(dataGridView);
            
        }

        public static void ShowErrorBox(Exception e){
            MessageBox.Show(TextHelper.Exception2String(e), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        public static List<FileInfo> getPhotoFileInfos(bool multiselect, String title=null) {
            OpenFileDialog OFD = new OpenFileDialog();
            List<FileInfo> files = new List<FileInfo>();
            OFD.Filter = "All Files (*.*)|*.*|" + DataHelper.ImageFormatFilter;
            if(title == null) {
                OFD.Title = multiselect ? "Bitte Fotos angeben" : "Bitte Foto angeben";
            } else {
                OFD.Title = title;
            }
            OFD.Multiselect = multiselect;
            if(OFD.ShowDialog() == DialogResult.OK) {
                files = (from String path in OFD.FileNames 
                         let fileInfo = new FileInfo(path)
                         where fileInfo.Exists == true 
                         select fileInfo
                        ).ToList();
            
            }
            return files;
        }

        
        /// <summary>
        /// Resets a Control/Form recursively
        /// </summary>
        /// <param name="whatever"></param>
        public static void ResetControls(Object whatever) {
            if(whatever is Form) {
                Form f  = (Form)whatever;
                foreach(Control c in f.Controls) {
                    FormHelper.ResetControls(c);
                }
            }else if(whatever is GroupBox) {
                foreach(Control c in ((GroupBox)whatever).Controls) {
                    FormHelper.ResetControls(c);
                }
            } else if(whatever is Panel) {
                foreach(Control c in ((Panel)whatever).Controls) {
                    FormHelper.ResetControls(c);
                }
            } else if(whatever is CheckBox) {
                ((CheckBox)whatever).Checked = false;
            } else if(whatever is ComboBox) {
                ((ComboBox)whatever).SelectedIndex = -1;
            } else if(whatever is DateTimePicker) {
                //String s = "";
                //((DateTimePicker)whatever).v.ValueChanged += new EventHandler(VAL_Required);
            } else if(whatever is TextBox) {
                TextBox tb = whatever as TextBox;
                tb.Clear();
                tb.Tag = null;
            } else if(whatever is UC_SetCoordinate) {
                ((UC_SetCoordinate)whatever).clear();
            } else if(whatever is DataGridView) {
                ((DataGridView)whatever).Rows.Clear();
            }
        }

        /// <summary>
        /// Resets a list of Form/Control Objects
        /// </summary>
        /// <param name="controls"></param>
        private void ResetControls(List<Object> controls) {
            foreach(Object whatever in controls) {
                FormHelper.ResetControls(whatever);
            }
        }


        /// <summary>
        /// Returns a list containing the selected rows of an DataGridView
        /// </summary>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static List<DataGridViewRow> getSelectedRows(DataGridView dgv) {
            List<int> rowids = new List<int>();
            List<DataGridViewRow> row_coll = new List<DataGridViewRow>();
            foreach(DataGridViewCell cell in dgv.SelectedCells) {
                rowids.Add(cell.RowIndex);
            }
            var rows = (from r in rowids select r).Distinct();
            rowids = rows.ToList<int>();
            foreach(int r_idx in rowids) {
                row_coll.Add(dgv.Rows[r_idx]);
            }
            return row_coll;
        }

        /// <summary>
        /// Saves a DataGridView as KML file.
        /// </summary>
        /// <param name="DGV"></param>
        /// <param name="kmlFile"></param>
        /// <param name="idColumn"></param>
        /// <param name="xValueColumn"></param>
        /// <param name="yValueColumn"></param>
        /// <param name="infoColumns"></param>
        /// <param name="selectedOnly"></param>
        public static void saveDGVasKML(DataGridView DGV, FileInfo kmlFile, 
                String idColumn, String xValueColumn, String yValueColumn,
                String[] infoColumns = null, bool selectedOnly=false) {

                    if(infoColumns == null) {
                        infoColumns = new String[] { idColumn, xValueColumn, yValueColumn };
                    }
                
                try {
                    CultureInfo englishCulture = CultureInfo.GetCultureInfo("en-GB");
                    
                   // String[] pointInfo = new String[] { "id", "name", "height", "slope", "longitude", "latitude", "notes" };
                    HashSet<int> locIDs = new HashSet<int>();
                    XmlDocument KML = new XmlDocument();
                    KML.AppendChild(KML.CreateXmlDeclaration("1.0", "UTF-8", String.Empty));
                    String ns = "http://www.opengis.net/kml/2.2";
                    XmlElement kmlNode = (XmlElement) KML.AppendChild(KML.CreateElement("kml"));
                    kmlNode.SetAttribute("xmlns", ns);
                    XmlElement docNode = KML.CreateElement("Document");
                    KML.LastChild.AppendChild(docNode);
                    StringBuilder SB = new StringBuilder();
                    foreach(DataGridViewRow row in DGV.Rows) {
                        if(row.Cells["longitude"].Value != DBNull.Value && row.Cells["latitude"].Value != DBNull.Value) {
                            int id = (int)row.Cells["id"].Value;
                            if(!locIDs.Contains(id) && 
                                ((!selectedOnly) || row.Selected)
                                ) {
                                
                                XmlElement placemark = (XmlElement) docNode.AppendChild(KML.CreateElement("Placemark"));
                                placemark.AppendChild(KML.CreateElement("name"));
                                placemark.LastChild.InnerXml = String.Format("{0}", id);
                                placemark.AppendChild(KML.CreateElement("description"));
                                
                                SB.AppendLine("<![CDATA[");
                                SB.AppendLine("CORE.LOCATIONS");
                                SB.AppendLine("<table border=\"0\">");
                                foreach(String entry in infoColumns) {
                                    if(DGV.Columns.Contains(entry) &&
                                        row.Cells[entry].Value != DBNull.Value) {
                                        SB.AppendLine("<tr>");
                                        SB.AppendFormat("<td>{0}</td>\n", entry);
                                        SB.AppendFormat(englishCulture, "<td>{0}</td>\n", 
                                            (row.Cells[entry].Value != DBNull.Value)?  
                                            row.Cells[entry].Value : ""
                                            );
                                        SB.AppendLine("</tr>");
                                    }
                                }
                                SB.AppendLine("</table>");    
                                SB.AppendLine("]]>");
                                placemark.LastChild.InnerXml = SB.ToString();
                                SB.Remove(0, SB.Length - 1);
                                
                                placemark.AppendChild(KML.CreateElement("Point"));
                                XmlElement coordinates = (XmlElement)placemark.LastChild.AppendChild(KML.CreateElement("coordinates"));
                                coordinates.InnerXml = String.Format(
                                    englishCulture, "{0}, {1}"
                                    , row.Cells["longitude"].Value
                                    , row.Cells["latitude"].Value);
                                if(row.Cells["height"].Value != DBNull.Value) {
                                    coordinates.InnerXml += String.Format(englishCulture, ",{0}"
                                        , row.Cells["height"].Value);
                                }
                                locIDs.Add(id);
                            }
                        }
                    }
                    
                    //KML.Schemas.Add(null, @"C:\Users\Benjamin\Documents\SPECLIB\SpectationClient\SpectationClient\conf\ogckml22.xsd");
                    //KML.Schemas.Add(null, @"Z:\KML\atom-author-link.xsd");
                    
                    //KML.Validate(set_ValidationEventHandler);

                    KML.Save(kmlFile.FullName);
                } catch(Exception ex) {
                    MessageBox.Show(TextHelper.Exception2String(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
        
        }


        public static void saveSpectraAsASD(List<SpectralTools.Spectrum> spectra, List<String> spectrumNames = null) {
            if(spectrumNames != null && spectra.Count != spectrumNames.Count) {
                throw new Exception("list spectra and list spectrumNames must have same number of elements");
            }
            
            FolderBrowserDialog FBD = new FolderBrowserDialog();
            FBD.Description = "Ordner für ASD Spektren auswählen";
            FBD.ShowNewFolderButton = true;

            if(FBD.ShowDialog() == DialogResult.OK) {
                String folder = FBD.SelectedPath;
                SpectralTools.ASD_Writer ASD_Writer = new SpectralTools.ASD_Writer();
                for(int i = 0; i < spectra.Count; i++) {
                    String name = (spectrumNames != null) ? spectrumNames[i] : spectra[i].SpectrumName;
                    name = name.Trim();
                    String fileName = String.Format("{0}\\{1}", folder, name);
                    FileInfo fi = new FileInfo(fileName);
                   
                    int cnt = 0;
                    fileName = DataHelper.createNewFilePath(fi, ref cnt).FullName;

                    ASD_Writer.createASDFile(fileName, spectra[i]);

                }
            }


        }

        public static void saveSpectraAsESL(List<SpectralTools.Spectrum> spectra , List<String> spectraNames = null) {
            SaveFileDialog SFD = new SaveFileDialog();
            SFD.AddExtension = true;
            SFD.DefaultExt = ".sli";
            SFD.Filter = "ENVI Spectral Library (*.sli)|*.sli";
            SFD.FileName = "spektren";

            if(SFD.ShowDialog() == DialogResult.OK) {
                try {
                    FileInfo fileInfo = new FileInfo(SFD.FileName);

                    SpectralTools.ESL_Writer ESL_Writer = new SpectralTools.ESL_Writer();

                    ESL_Writer.createESLFiles(fileInfo, spectra);
                } catch(Exception e) {
                    FormHelper.ShowErrorBox(e);
                }
            }
        
        
        }

        /// <summary>
        /// Struct used to relate a database ID to a file name
        /// </summary>
        private struct DBFileInfo {

            public string Filepath { get; set; }
            public Object[] DBid { get; set; }
            public Image Image { get; set; }

        }

        public static void saveDGV(DataGridView DGV, FileInfo pathCSVTable, bool selectedOnly, bool useDB){
            FormHelper.saveDGV(DGV, pathCSVTable, selectedOnly, useDB,
                ";",
                SpectralTools.Spectrum.SpectrumFileType.ESL,
                ImageFormat.Png);
            
        }

        public static void saveDGV(DataGridView DGV, FileInfo pathCSVTable, bool selectedOnly, bool useDB,
            //String[] excludedColumns,
            String separator,
            SpectralTools.Spectrum.SpectrumFileType spectrumFileType, ImageFormat imageFormat
                    ) {
           
            //if(excludedColumns == null) excludedColumns = new String[] { };
            if(imageFormat == null) imageFormat = ImageFormat.Png;
            if(separator == null) separator = ";";


            DirectoryInfo rootDir = pathCSVTable.Directory;
            DirectoryInfo blobDIR = null;

            String baseName = Regex.Replace(pathCSVTable.FullName, @"\.csv$", "", RegexOptions.IgnoreCase);
            String tableName = baseName + ".csv";


            Dictionary<String, DirectoryInfo> BLOBDirectories = new Dictionary<string,DirectoryInfo>();
            SpectralTools.ASD_Writer ASD_Writer = new SpectralTools.ASD_Writer();
            SpectralTools.ESL_Writer ESL_Writer = new SpectralTools.ESL_Writer();

            StringBuilder SB = new StringBuilder();
            
            
            //Create Header line
            String[] columnNames = (from DataGridViewColumn dgvCol in DGV.Columns
                                    select dgvCol.Name).ToArray();
            List<String> BLOBColumns = (from DataGridViewColumn dgvCol in DGV.Columns
                                        where dgvCol is DataGridViewSpectrumColumn ||
                                            dgvCol is DataGridViewPhotoColumn ||
                                            dgvCol is DataGridViewByteArrayColumn ||
                                            dgvCol.ValueType == typeof(Byte[])
                                        select dgvCol.Name).ToList();
            //Remove blob columns that serve as spectrum header columns, since they are handeled by the 
            //spectrum file column
            var temp = from DataGridViewColumn dgvCol in DGV.Columns
                    where dgvCol is DataGridViewSpectrumColumn
                    select (dgvCol as DataGridViewSpectrumColumn);
            foreach(DataGridViewSpectrumColumn c in temp) {
                if(c.ColumnInfo != null &&
                   c.ColumnInfo.HeaderFileColumn != null &&
                   BLOBColumns.Contains(c.ColumnInfo.HeaderFileColumn.Name)) {
                       BLOBColumns.Remove(c.ColumnInfo.HeaderFileColumn.Name);
                }
            
            }

            if(BLOBColumns.Count > 0 && blobDIR == null) {
                blobDIR = rootDir.CreateSubdirectory((new FileInfo(baseName)).Name /*+ "_files"*/);
            }

            SB.AppendLine(String.Join(separator, columnNames));

            //Save spectra in case of ESL output
            bool writeESL = spectrumFileType == SpectralTools.Spectrum.SpectrumFileType.ESL;
            Dictionary<Object, Byte[]> Images = new Dictionary<object, byte[]>();

            if(writeESL) { 
              
                var specColumns = from DataGridViewColumn c in DGV.Columns
                        where c is DataGridViewSpectrumColumn
                        select c;
                foreach(DataGridViewSpectrumColumn c in specColumns){
                    int iColumn = c.Index;
                    var y = (from DataGridViewRow row in DGV.Rows
                            where row.Cells[iColumn] != null && (!selectedOnly || row.Selected)
                            select ((DataGridViewSpectrumCell) row.Cells[iColumn]).getSpectrum()).ToList();
                    
                    if(y.Count > 0){
                        String path = String.Format("{0}\\{1}", blobDIR.FullName, c.Name);
                        ESL_Writer.createESLFiles(new FileInfo(path), y);
                    }

                }
            }

            //Photo columns 
            var xPhotoColumns = from DataGridViewColumn c in DGV.Columns
                               let pC = c as DataGridViewPhotoColumn
                               where pC != null && !pC.IsPreviewColumn
                               select pC;




            Dictionary<Int32, List<DBFileInfo>> photoColumnIDs = new Dictionary<int, List<DBFileInfo>>();
            foreach(DataGridViewPhotoColumn pc in xPhotoColumns) {
                photoColumnIDs.Add(pc.Index, new List<DBFileInfo>());
            }           

            //Create Table Content
            List<String> lineValues = new List<string>();
            foreach(DataGridViewRow dgvRow in DGV.Rows){
                if(!selectedOnly && !dgvRow.Selected) continue;
                int iRow = dgvRow.Index;
                foreach(DataGridViewColumn dgvCol in DGV.Columns) {
                    int iCol = dgvCol.Index;
                    DataGridViewCell cell = DGV[iCol, iRow];

                    if(cell.Value == null){
                        lineValues.Add("");
                    }else{
                        if(BLOBColumns.Contains(dgvCol.Name)) {
                            //Create directory containing CSV table related BLOBs
                            
                            String shortname = String.Format("{0}_{1}", dgvCol.Name, iRow+1);
                            lineValues.Add(shortname);
                            String filename = blobDIR.FullName + "\\" + shortname;
                            

                            //Write files
                            if(cell is DataGridViewSpectrumCell){
                                if(!writeESL){
                                   ASD_Writer.createASDFile(filename + ".asd", 
                                       ((DataGridViewSpectrumCell)cell).getSpectrum() );
                                }
                            } else if(cell is DataGridViewPhotoCell && photoColumnIDs.Keys.Contains(cell.ColumnIndex)){
                                DataGridViewPhotoCell photoCell = cell as DataGridViewPhotoCell;
                               
                                Image img = photoCell.getImage();
                                String imgageFilename = filename + "." + imageFormat.ToString().ToLower();
                                if(img != null) {
                                    img.Save(imgageFilename, imageFormat);
                                } else if(useDB){
                                    DBFileInfo info = new DBFileInfo();
                                    info.Filepath = imgageFilename;
                                    info.DBid = photoCell.getRowID();
                                    photoColumnIDs[cell.ColumnIndex].Add(info);
                                }

                            } else if(cell is DataGridViewByteArrayCell) {
                                DataGridViewByteArrayCell baCell = (DataGridViewByteArrayCell)cell;
                                FileInfo fi = new FileInfo(filename);
                                if(baCell.File != null) {
                                    File.WriteAllBytes(filename, baCell.File);
                                }
                                if(baCell.FileHeader != null) {
                                    File.WriteAllBytes(filename + ".hdr", baCell.FileHeader);
                                }

                            }
                        } else {
                            lineValues.Add(String.Format("{0}", cell.Value));
                        }
                    }
                    
                
                }

                SB.AppendLine(String.Join(separator, lineValues));
                lineValues.Clear();
            }
            File.WriteAllText(pathCSVTable.FullName, SB.ToString());


            //load and save database BLOBs
            foreach(int columnID in photoColumnIDs.Keys) {
                DataGridViewPhotoColumn pC = DGV.Columns[columnID] as DataGridViewPhotoColumn;
                List<DBFileInfo> infoList = photoColumnIDs[columnID];
                
                List<Object[]> idList = (from DBFileInfo info in infoList
                                         select info.DBid).ToList();
                if(idList.Count == 0) continue;

                //Build SQL command as daisy chain
                TableInfo ti = pC.TableInfo;
                int nIDColumns = idList[0].Count();
                String idString = String.Join(",", ti.PrimaryKey.Names);

                int pCount = 0;
                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.Connection = pC.Connection;
                String[] parTupel = new String[nIDColumns];
                foreach(Object[] idTupel in idList) {
                    cmd.CommandText += String.Format("SELECT {0}, {1} FROM {2} WHERE ({1}) = ", 
                                            pC.ColumnInfo.Name, idString, ti.SchemaTableName);
                    for(int i=0; i< nIDColumns; i++) {
                        String par = String.Format("@p{0}", pCount++);
                        cmd.Parameters.AddWithValue(par, idTupel[i]);
                        parTupel[i] = par;
                    }
                    cmd.CommandText += "("+String.Join(",",parTupel)+");\n";
                }

                //Run the database request
                cmd.Connection.Open();
                try {
                    NpgsqlDataReader dr = cmd.ExecuteReader();
                    int i = 0;
                    do {
                        while(dr.Read()) {
                            if(!dr.IsDBNull(0)){
                                Byte[] blob = dr.GetValue(0) as Byte[]; 
                                Image img = DataHelper.ByteArrayToImage(blob);
                                String path = infoList[i++].Filepath;
                                if(img != null) {
                                    img.Save(path, imageFormat);
                                } else {
                                    File.WriteAllBytes(path, blob);
                                }
                            }
                        }
                    } while(dr.NextResult());


                }catch(Exception ex){
                    throw ex;
                
                } finally {
                    cmd.Connection.Close();        
                }
                
                

               
            }
           


        }

        

    }


}
